package com.miao.hong.fang.course.dao;

import java.util.ArrayList;
import java.util.List;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.ProjectionList;

import com.miao.hong.fang.base.dao.BaseDAO;
import com.miao.hong.fang.base.dao.PaginationEnabler;
import com.miao.hong.fang.course.CourseTO;
import com.miao.hong.fang.teacher.TeacherTO;
import com.miao.hong.fang.util.StringUtil;
import com.miao.hong.fang.util.Utils;
import com.miao.hong.fang.util.pagination.ListAndCount;

public class CourseDAOImpl extends BaseDAO implements CourseDAO{

	public List<CourseTO> getHotCourse() {
		
		DetachedCriteria criteria = DetachedCriteria.forClass(CourseTO.class);
		criteria.add(Restrictions.eq("isHotCourse", "Y")).add(Restrictions.gt("courseStartTime", Utils.getCurrentDate()));
		
		getHibernateTemplate().setCacheQueries(true);
		
		ProjectionList proList = Projections.projectionList();
		proList.add(Projections.groupProperty("courseId"));
		proList.add(Projections.groupProperty("courseTitle"));
		criteria.setProjection(proList);
		
		List<Object[]> list = getHibernateTemplate().findByCriteria(criteria);
		List<CourseTO> result = new ArrayList<CourseTO>();
		CourseTO to = null;
		for(Object[] o : list) {
			
			to = new CourseTO();
			to.setCourseId((String)o[0]);
			to.setCourseTitle((String)o[1]);
			result.add(to);
			
		}
		return result;
	}
	
	public List getRecommendedCourse() {

		DetachedCriteria criteria = DetachedCriteria.forClass(CourseTO.class);
		criteria.add(Restrictions.eq("isRecommendCourse", "Y")).add(Restrictions.gt("courseStartTime", 
				Utils.getCurrentDate()));
		
		getHibernateTemplate().setCacheQueries(true);
		return getHibernateTemplate().findByCriteria(criteria);
	}
	
	// public  effective
	public ListAndCount getPublicCourse(Timestamp courseStartDt, String location, String category) {
		
		DetachedCriteria criteria = DetachedCriteria.forClass(CourseTO.class);
		criteria.add(Restrictions.eq("isPublicCourse", "Y"));
		
		if(courseStartDt == null) {
			criteria.add(Restrictions.ge("courseStartTime", Utils.getCurrentDate()));
		}else {
			criteria.add(Restrictions.ge("courseStartTime", courseStartDt));
		}
		if(location != null) {
			criteria.add(Restrictions.eq("courseCity", location));
		}
		if(category != null) {
			criteria.add(Restrictions.eq("courseCategory", category));
		}
		getHibernateTemplate().setCacheQueries(true);
		return PaginationEnabler.queryForList(getHibernateTemplate(), criteria);
	}
	
	public List getCityCourse(String cityName) {
		
		DetachedCriteria criteria = DetachedCriteria.forClass(CourseTO.class);
		criteria.add(Restrictions.gt("courseStartTime", Utils.getCurrentDate())).
												add(Restrictions.eq("courseCity", cityName));
		
		getHibernateTemplate().setCacheQueries(true);
		return getHibernateTemplate().findByCriteria(criteria);
	}
	
	public void addCourse(final CourseTO courseTO) {
		
		getHibernateTemplate().save(courseTO);				
	}

	public ListAndCount searchCourse(String courseTitle, Object coursePeriod, Timestamp courseStartTime, Timestamp courseEndTime, Object coursePrice, 
			String courseCity, String courseCategory, String courseTeacher, String isHotCourse, String isRecommendCourse, String isPublicCourse) {

		DetachedCriteria criteria = DetachedCriteria.forClass(CourseTO.class);
		
		if(StringUtil.isNotEmpty(courseTitle)) {
			criteria.add(Restrictions.like("courseTitle", courseTitle, MatchMode.ANYWHERE));
		}
		if(coursePeriod != null) {
			criteria.add(Restrictions.le("coursePeriod", coursePeriod));
		}
		if(courseStartTime != null) {
			criteria.add(Restrictions.ge("courseStartTime", courseStartTime));			
		}
		if(courseEndTime != null) {
			criteria.add(Restrictions.le("courseEndTime", courseEndTime));
		}
		if(coursePrice != null) {
			criteria.add(Restrictions.le("coursePrice", coursePrice));
		}
		if(StringUtil.isNotEmpty(courseCity)) {
			criteria.add(Restrictions.eq("courseCity", courseCity));
		}
		if(StringUtil.isNotEmpty(courseCategory)) {
			criteria.add(Restrictions.eq("courseCategory", courseCategory));
		}
		if(StringUtil.isNotEmpty(courseTeacher)) {
			criteria.add(Restrictions.eq("courseTeacher", courseTeacher));
		}
		if(StringUtil.isNotEmpty(isHotCourse)) {
			criteria.add(Restrictions.eq("isHotCourse", isHotCourse));
		}
		if(StringUtil.isNotEmpty(isRecommendCourse)) {
			criteria.add(Restrictions.eq("isRecommendCourse", isRecommendCourse));
		}
		if(StringUtil.isNotEmpty(isPublicCourse)) {
			criteria.add(Restrictions.eq("isPublicCourse", isPublicCourse));
		}

		return PaginationEnabler.queryForList(getHibernateTemplate(), criteria);		
	}

	public void updateCourse(CourseTO courseTO) {
		getHibernateTemplate().update(courseTO);
	}
	
	public void deleteCourse(String courseId, String version) {
		CourseTO courseTO = new CourseTO();
		courseTO.setCourseId(courseId);
		courseTO.setVersion(Integer.valueOf(version));
		
		getHibernateTemplate().delete(courseTO);
	}
	
	public CourseTO getCourseDetail(String courseId) {
		
		return (CourseTO) getHibernateTemplate().get(CourseTO.class, courseId);
	}
	
	public List getAllCourseCities() {
		return getJdbcTemplate().queryForList("select distinct COURSE_CITY from tbl_course", String.class);
	}
	
	public List getAllCourseCategory() {
		
		return (List) getHibernateTemplate().execute(
				new HibernateCallback() {					
					public Object doInHibernate(Session session) throws SQLException{
						
						List<String> list = new ArrayList<String>();
						Connection conn = session.connection();
						PreparedStatement ps = null;
						try {
							ps = conn.prepareStatement("select CATEGORY_NAME from tbl_course_category");
							ResultSet rs = ps.executeQuery();
							while(rs.next()) {
								list.add(rs.getString(1));
							}
						}catch (SQLException e) {
							throw new SQLException(e.getMessage());
						}finally {
							if(ps != null) {
								ps.close();
							}
						}
						return list;
					}					
				}
		);
	}
	
	public void addCourseCategory(String courseCategory){
		String sql = "insert into tbl_course_category values (?)";
		getJdbcTemplate().update(sql, new Object[]{courseCategory});
	}
	
	public int updateCourseCategory(String oldValue, String newValue) {
		
		String deleteSQL = "delete from tbl_course_category where CATEGORY_NAME = ?";
		getJdbcTemplate().update(deleteSQL, new Object[]{oldValue});
		
		addCourseCategory(newValue);
		
		removeFromCache("select teacher_id from tbl_teacher where teach_major = '" + oldValue + "'", TeacherTO.class);
		String updateTeacherSQL = "update tbl_teacher set TEACH_MAJOR = ? where TEACH_MAJOR = ?";
		Object[] args = {newValue, oldValue};
		getJdbcTemplate().update(updateTeacherSQL, args);
		
		removeFromCache("select course_id from tbl_course where course_category = '" + oldValue + "'", CourseTO.class);
		String updateCourseSQL = "update tbl_course set COURSE_CATEGORY = ? where COURSE_CATEGORY = ?";
		int j = getJdbcTemplate().update(updateCourseSQL, args);

		return j;
	}
	
	public int deleteCourseCategory(String categoryName) {
	
		removeFromCache("select course_id from tbl_course where course_category = '" + categoryName + "'", CourseTO.class);
		String deleteSQL = "delete from tbl_course where COURSE_CATEGORY = ?";
		getJdbcTemplate().update(deleteSQL, new Object[]{categoryName});
		
		removeFromCache("select teacher_id from tbl_teacher where teach_major = '" + categoryName + "'", TeacherTO.class);
		deleteSQL = "delete from tbl_teacher where TEACH_MAJOR = ?";
		getJdbcTemplate().update(deleteSQL, new Object[]{categoryName});
		
		deleteSQL = "delete from tbl_course_category where CATEGORY_NAME = ?";
		return getJdbcTemplate().update(deleteSQL, new Object[]{categoryName});
	}

	public List getCourseByCategory(String courseCategory) {
		DetachedCriteria criteria = DetachedCriteria.forClass(CourseTO.class);
		criteria.add(Restrictions.eq("courseCategory", courseCategory));
		
		getHibernateTemplate().setCacheQueries(true);
		return getHibernateTemplate().findByCriteria(criteria);
	}
	
	public List getAllCourse() {
		return getHibernateTemplate().find("from CourseTO");
	}
	
}
